package cj.web.marry.dao;

import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;

@DAO
public interface XQPubYHDAO {
	
	@SQL("select * from (SELECT isOpen,followCount,createAt,deleteFlag,id,userId,type,name,nickname,sex,identityCard,photo,education,profession,province,city,area,money,DATE_FORMAT(NOW(), '%Y') - SUBSTRING(identityCard, 7, 4) AS age FROM MemberDetail) a "
			+ "WHERE a.deleteFlag=1 AND a.type=:1 AND a.userId not in (:4) AND a.userId in (:14)"
			+ "#if(:6!=''){ AND a.education =:6} #if(:7!=''){ AND a.profession =:7} "
			+ "#if(:8!=-1){ AND a.money =:8} #if(:9!=''){ AND a.area =:9} "
			+ "#if(:10!=''){ AND a.city =:10} #if(:11!=''){ AND a.province =:11} "
			+ "#if(:13!=''){ AND a.age >=:13} "
			+ "#if(:12==0){ ORDER BY a.createAt DESC } "
			+ "#if(:12==1){ ORDER BY a.followCount DESC } "
			+ "#if(:12==2){ ORDER BY age } "
			+ "#if(:12==3){ ORDER BY age DESC } "
			+ "LIMIT :2,:3")
    List<Map> queryMemberDetailLisForPub(int type,int start,int length,int userId,int sex,
    		String education,String profession,int money,String area,String city,
    		String province,String dataOrder,String agePicker,List<Integer> userIds);
	
	@SQL("select * from (SELECT isOpen,followCount,createAt,deleteFlag,id,userId,type,name,nickname,sex,identityCard,photo,education,profession,province,city,area,money,DATE_FORMAT(NOW(), '%Y') - SUBSTRING(identityCard, 7, 4) AS age FROM MemberDetail) a "
			+ "WHERE a.deleteFlag=1 AND a.type=:1 AND a.userId not in (:4) AND a.sex <> :5 "
			+ "#if(:6!=''){ AND a.education =:6} #if(:7!=''){ AND a.profession =:7} "
			+ "#if(:8!=-1){ AND a.money =:8} #if(:9!=''){ AND a.area =:9} "
			+ "#if(:10!=''){ AND a.city =:10} #if(:11!=''){ AND a.province =:11} "
			+ "#if(:13!=''){ AND a.age >=:13} "
			+ "#if(:12==0){ ORDER BY a.createAt DESC } "
			+ "#if(:12==1){ ORDER BY a.followCount DESC } "
			+ "#if(:12==2){ ORDER BY age } "
			+ "#if(:12==3){ ORDER BY age DESC } "
			+ "LIMIT :2,:3")
    List<Map> queryMemberDetailLis(int type,int start,int length,int userId,int sex,
    		String education,String profession,int money,String area,String city,
    		String province,String dataOrder,String agePicker);
	
//	@SQL("SELECT count(1) FROM MemberDetail WHERE deleteFlag=1 AND type=:1 AND userId not in (:2) AND sex <> :3 ")
//    int queryMemberDetailLisSize(int type,int userId,int sex);
	
	@SQL("update MemberDetail set followCount=followCount+1 where userId=:1")
	void updateFollowCountAdd(int id);
	
	@SQL("update MemberDetail set followCount=followCount-1 where userId=:1")
	void updateFollowCountMinus(int id);
	
	@SQL("INSERT INTO PubYH (userId,objectUserId,appointDate,address,descs,appointStatus,deleteFlag,createAt,createUser,updateAt,updateUser) VALUES (:1,:2,:3,:4,:5,:6,1,now(),:7,now(),:7)")
	void create(int userId,int objectUserId,String appointDate,String address,String descs,int appointStatus,String phone);

	@SQL("SELECT a.*,b.type,b.name,b.nickname,b.sex,b.identityCard,b.photo,b.education,b.profession,b.province,b.city,b.area,b.isOpen FROM PubYH a join MemberDetail b on a.objectUserId=b.userId WHERE a.deleteFlag=1 #if(:1!=''){ AND a.userId =:1} ORDER BY a.updateAt DESC LIMIT :2,:3 ")
    List<Map> queryPubYH(int userId,int start,int length);
		
	@SQL("SELECT count(1) FROM PubYH WHERE deleteFlag=1 #if(:1!=''){ AND userId =:1} ")
    int queryPubYHSize(int userId);
    
    @SQL("SELECT *,date_format(appointDate,'%Y-%m-%d %T') as appointDate FROM PubYH WHERE id=:1 ")
    List<Map> queryPubYHById(int id);
    
    @SQL("SELECT count(1) FROM PubYH WHERE deleteFlag=1 AND userId =:1 and objectUserId=:2 and appointStatus in (##(:3)) ")
    int queryPubYHSize(int userId,int objectUserId,String appointStatus);
}
